﻿using Abp.Domain.Repositories;
using Abp.EntityFramework;
using Abp.ObjectMapping;
using AVIS.OutBound.Cities;
using AVIS.OutBound.EntityFramework.Models;
using AVIS.OutBound.Stores;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;

namespace AVIS.OutBound.EntityFramework.Repositories
{
    public class StoreRepository : OutBoundRepositoryBase<Store>, IStoreRepository
    {
        private readonly IRepository<AvisORLocation> _avisORLocationRepository;
        private readonly IRepository<AvisStore> _avisStoreRepository;
        private readonly IRepository<AvisORLocshr> _avisORLocshrRepository;
        private readonly IRepository<City> _cityRepository;
        private readonly IObjectMapper _objectMapper;

        public StoreRepository(IDbContextProvider<OutBoundDbContext> dbContextProvider,
            IRepository<AvisORLocation> avisORLocationRepository,
            IRepository<AvisStore> avisStoreRepository,
            IRepository<AvisORLocshr> avisORLocshrRepository,
            IRepository<City> cityRepository,
            IObjectMapper objectMapper)
            : base(dbContextProvider)
        {
            _avisORLocationRepository = avisORLocationRepository;
            _avisStoreRepository = avisStoreRepository;
            _avisORLocshrRepository = avisORLocshrRepository;
            _cityRepository = cityRepository;
            _objectMapper = objectMapper;
        }

        /// <summary>
        /// 获取指定Code的门店
        /// </summary>
        /// <param name="storeCode"></param>
        /// <returns></returns>
        public Store GetStoreByCode(string storeCode)
        {
            var avisLocation = _avisORLocationRepository.FirstOrDefault(s => storeCode.Equals(s.LocationCode));
            var avisStore = _avisStoreRepository.FirstOrDefault(o => storeCode.Contains(o.Code));
            var avisORLocshr = _avisORLocshrRepository.FirstOrDefault(o => storeCode.Contains(o.LocationCode));

            return BuildStore(avisLocation, avisStore, avisORLocshr);
        }

        /// <summary>
        /// 获取指定城市的门店（包含附近门店）
        /// </summary>
        /// <param name="cityCode"></param>
        /// <param name="regionCode"></param>
        /// <returns></returns>
        public IList<Store> GetStoresByCity(string cityCode, string regionCode)
        {
            // 获取指定城市的门店信息（AvisORLocation）
            var avisLocations = GetORLocationByCityCode(cityCode, regionCode);
            var codes = avisLocations.Select(t => t.LocationCode).ToArray();

            // AvisStore
            var avisStores = _avisStoreRepository.GetAllList(o => codes.Contains(o.Code));

            // AvisORLocshr
            var shopHours = _avisORLocshrRepository.GetAllList(o => codes.Contains(o.LocationCode));

            var list = new List<Store>(avisLocations.Count);
            foreach (var location in avisLocations)
            {
                var store = avisStores.FirstOrDefault(s => s.Code == location.LocationCode);
                var locshr = shopHours.FirstOrDefault(g => g.LocationCode == location.LocationCode);
                list.Add(BuildStore(location, store, locshr));
            }
            return list;
        }

        /// <summary>
        /// 搜索门店信息
        /// </summary>
        /// <param name="searchKey">搜索关键字</param>
        /// <returns></returns>
        public IList<Store> QueryStore(string searchKey)
        {
            string sql = @"select top 50 Id,LocationCode as Code,LocationName as ENName,StoresCNName as CNName,
                                    RegionCode,LocationType,CountryCNName as CountryName,
                                    CityCNName as CityName,LocationCity as CityCode
                            from VW_OBStores WITH(NOLOCK) 
                            where CountryCNName like '%'+ @k + '%' ESCAPE N'~' 
                                    or CountryName like '%'+ @k + '%' ESCAPE N'~' 
                                    or RegionCNName like '%'+ @k + '%' ESCAPE N'~' 
                                    or CityCNName like '%'+ @k + '%' ESCAPE N'~' 
                                    or LocationCity like '%'+ @k + '%' ESCAPE N'~' 
                                    or CNPY like '%'+ @k + '%' ESCAPE N'~' 
                                    or StoresCNName like '%'+ @k + '%' ESCAPE N'~' 
                            order by CNPY asc";

            var queryList = Context.Database.SqlQuery<Store>(sql, new SqlParameter("@k", searchKey)).ToList();
            var list = new List<Store>();
            foreach (var loc in queryList.OrderBy(s => s.StoreType)) // 按门店排序
            {
                list.Add(new Store()
                {
                    Id = loc.Id,
                    Code = loc.Code?.Trim(),
                    CNName = loc.CNName?.Trim(),
                    ENName = loc.ENName?.Trim(),
                    LocationType = loc.LocationType,
                    RegionCode = loc.RegionCode?.Trim(),
                    CityCode = loc.CityCode?.Trim(),
                    CityName = loc.CityName,
                    CountryName = loc.CountryName?.Trim(),
                });
            }

            #region linq方式，性能需要优化
            //var query = from s in _avisORLocationRepository.GetAll()
            //            join store in _avisStoreRepository.GetAll()
            //                on s.LocationCode equals store.Code
            //            join city in _cityRepository.GetAll()
            //                on new { a1 = s.LocationCity, a2 = s.RegionCode, a3 = s.AGDCode } equals new { a1 = city.ENName, a2 = city.RegionCode, a3 = city.AGDCode } into cityJoin
            //            from joinedCity in cityJoin.DefaultIfEmpty()
            //            where s.LocationCity.Contains(searchKey) || joinedCity.CNName.Contains(searchKey)
            //                || s.RegionName.Contains(searchKey) || s.LocationCity.Contains(searchKey)
            //                || s.LocationName.Contains(searchKey) || s.LocationCode.Contains(searchKey)
            //            select new
            //            {
            //                s.Id,
            //                s.LocationCode,
            //                s.LocationName,
            //                store.CNName,
            //                s.RegionCode,
            //                s.LocationType,
            //                s.LocationCity,
            //                s.AGDCode,
            //                store.NewADGCode,
            //                CityName = joinedCity.CNName,
            //            };


            //foreach (var loc in query.ToList())
            //{
            //    list.Add(new Store()
            //    {
            //        Id = loc.Id,
            //        Code = loc.LocationCode.Trim(),
            //        CNName = string.IsNullOrWhiteSpace(loc.CNName) ? loc.LocationName.Trim() : loc.CNName.Trim(),
            //        ENName = loc.LocationName.Trim(),
            //        LocationType = loc.LocationType,
            //        RegionCode = string.IsNullOrWhiteSpace(loc.RegionCode) ? "" : loc.RegionCode.Trim(),
            //        CityCode = string.IsNullOrWhiteSpace(loc.LocationCity) ? "" : loc.LocationCity.Trim(),
            //        CityName = loc.CityName,
            //        AGDCode = loc.AGDCode,
            //        NewADGCode = loc.NewADGCode,
            //    });
            //}
            #endregion

            return list;
        }

        #region 私有方法
        private List<AvisORLocation> GetORLocationByCityCode(string cityCode, string regionCode)
        {
            var list = new List<AvisORLocation>();

            // 1. 查询指定城市里所有门店的附近门店AGDCode（AGDCode != "999"）
            //        注意 LocationCity 的值有空格，需要采用Trim()
            var agdCodes = (from s in _avisORLocationRepository.GetAll()
                            where s.LocationCity.Trim() == cityCode && s.RegionCode == regionCode && s.AGDCode != "999"
                            select s.AGDCode).Distinct().ToList();

            // 2.1 查询指定城市的门店（包含没有附近门店的门店）
            var list1 = _avisORLocationRepository.GetAllList(
                s => agdCodes.Contains(s.AGDCode) || (s.AGDCode == "999" && s.LocationCity == cityCode && s.RegionCode == regionCode));

            list.AddRange(list1);

            // 2.2 查询包含在附近门店AGDCode的所有门店信息（如：上海市内的所有门店及上海周边的所有门店）
            var storeCodes = (from s in _avisStoreRepository.GetAll()
                              where agdCodes.Contains(s.NewADGCode)
                              select s.Code).ToList();
            var list2 = _avisORLocationRepository.GetAllList(s => storeCodes.Contains(s.LocationCode));

            // 3 合并门店数据（排查重复的）
            var locationCodes = list1.Select(s => s.LocationCode);
            list2.ForEach(item =>
            {
                if (!locationCodes.Contains(item.LocationCode))
                {
                    list.Add(item);
                }
            });

            // 4. 按LocationType，ENName进行排序
            return list.OrderBy(s => s.LocationType).ThenBy(s => s.SeqNo).ThenBy(s => s.LocationName).ToList();
        }

        private Store BuildStore(AvisORLocation location, AvisStore avisStore, AvisORLocshr locshr)
        {
            // 绑定AvisORLocation
            var store = _objectMapper.Map<Store>(location);

            // 绑定AvisStore
            if (avisStore != null)
            {
                // 周边门店的编号
                store.NewADGCode = avisStore.NewADGCode;
                store.IsHot = avisStore.IsTop.HasValue ? avisStore.IsTop.Value : false;

                // 中文名字
                if (!string.IsNullOrEmpty(avisStore.CNName))
                {
                    store.CNName = avisStore.CNName.Trim();
                }

                // 门店的经度
                double Longitude = 0;
                if (double.TryParse(avisStore.Longitude, out Longitude))
                {
                    store.Longitude = Longitude;
                }

                // 门店的纬度
                double Latitude = 0;
                if (double.TryParse(avisStore.Latitude, out Latitude))
                {
                    store.Latitude = Latitude;
                }
            }

            // 绑定AvisORLocshr（营业时间）
            store.StoreHours = _objectMapper.Map<StoreHours>(locshr);

            return store;
        }

        #endregion
    }
}
